/**
 *  Created on  : 25 Noviembre de 2010
 *  Author      : Javier Silva Pérez
 *  Description :
 *              Clase estatica que contiene todas las instrucciones referentes a
 *              la Base de datos (SELECTs, INSERTs, UPDATEs, etc) en forma de
 *              PreparedStatements
 */
package cinvestav.adviseme.util;




/**
 *
 * @author Javier Silva Pérez
 */
public class DataBaseDictionary {
	
    //<editor-fold defaultstate="collapsed" desc="Alias Tablas">

    public static final String ALIAS_PERSONA = "per";
    public static final String ALIAS_PERSONA_SOLICITANTE = "solicitante";
    public static final String ALIAS_PERSONA_SOLICITADO = "solicitado";
    public static final String ALIAS_SOLICITUD = "sol";
    public static final String ALIAS_LUGAR = "lug";
    public static final String ALIAS_AMIGOSPORPERSONA = "axp";
    public static final String ALIAS_AMIGO = "amigo";
    public static final String ALIAS_COMENTARIO = "comment";
    public static final String ALIAS_PERSONALUGAR = "perlug";
    public static final String ALIAS_FOTO = "foto";
    public static final String ALIAS_TIPOLUGAR = "tip";
    
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="Posibles Tipos">
    public static final String STRING_SIMPLE = "String";
    public static final String STRING_JOKER = "StringL";
    public static final String STRING_DOBLE_JOKER = "LStringL";
    public static final String INTEGER_TYPE = "int";
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="SELECTs">
    public static final String GET_ALL_PERSONAS = "SELECT * From Persona";

    public static final String GET_BASE_PERSONAS = "SELECT * From Persona " 
    	+ DataBaseDictionary.ALIAS_PERSONA + " ";

    public static final String GET_ALL_LUGARES = "SELECT * From Lugar as l INNER JOIN TipoLugar as tl ON tl.ide_tip=l.ide_tip";
    public static final String GET_ALL_SOLICITUDES = "SELECT * from Solicitud s,Persona p1,Persona p2 WHERE s.ide_ori = p1.ide_per AND s.ide_des = p2.ide_per";
    public static final String GET_ALL_TIPO_LUGAR = "SELECT * FROM TipoLugar";    
    
    
    public static final String GET_BASE_COMENTARIOS = "SELECT * FROM Comentario "
        + DataBaseDictionary.ALIAS_COMENTARIO +" INNER JOIN Persona "+ DataBaseDictionary.ALIAS_PERSONA+" ON "+DataBaseDictionary.ALIAS_PERSONA+
        ".ide_per ="+DataBaseDictionary.ALIAS_COMENTARIO+".ide_per WHERE";
    
//    select * from Foto f INNER JOIN Persona p ON p.ide_per = f.ide_per INNER JOIN Lugar l ON l.ide_lug = f.ide_lug;
    public static final String GET_BASE_FOTOS = "SELECT * FROM Foto "+DataBaseDictionary.ALIAS_FOTO + " INNER JOIN Persona "+ DataBaseDictionary.ALIAS_PERSONA+
    				" ON "+DataBaseDictionary.ALIAS_PERSONA+".ide_per ="+DataBaseDictionary.ALIAS_FOTO+".ide_per INNER JOIN Lugar "+
    				DataBaseDictionary.ALIAS_LUGAR +" ON "+DataBaseDictionary.ALIAS_LUGAR+".ide_lug = "+DataBaseDictionary.ALIAS_FOTO+".ide_lug INNER JOIN TipoLugar "+
    				DataBaseDictionary.ALIAS_TIPOLUGAR+" ON "+DataBaseDictionary.ALIAS_TIPOLUGAR+".ide_tip ="+DataBaseDictionary.ALIAS_LUGAR+".ide_tip"
    				+" WHERE";
    
	public static final String GET_BASE_FRIEND = "SELECT * From Persona "
			+ DataBaseDictionary.ALIAS_PERSONA
			+ " INNER JOIN AmigosPorPersona "
			+ DataBaseDictionary.ALIAS_AMIGOSPORPERSONA + " ON "
			+ DataBaseDictionary.ALIAS_PERSONA + ".ide_per="
			+ DataBaseDictionary.ALIAS_AMIGOSPORPERSONA
			+ ".ide_per INNER JOIN Persona " + DataBaseDictionary.ALIAS_AMIGO
			+ " ON " + DataBaseDictionary.ALIAS_AMIGO + ".ide_per="
			+ DataBaseDictionary.ALIAS_AMIGOSPORPERSONA + ".ide_ami WHERE "+DataBaseDictionary.ALIAS_PERSONA+".ide_per = ?";
    
	public static final String GET_BASE_LUGAR = "SELECT * FROM Lugar "
			+ DataBaseDictionary.ALIAS_LUGAR + " INNER JOIN TipoLugar "
			+ DataBaseDictionary.ALIAS_TIPOLUGAR + " ON "
			+ DataBaseDictionary.ALIAS_LUGAR + ".ide_tip="
			+ DataBaseDictionary.ALIAS_TIPOLUGAR
			+ ".ide_tip INNER JOIN PersonaLugar "
			+ DataBaseDictionary.ALIAS_PERSONALUGAR + " ON "
			+ DataBaseDictionary.ALIAS_LUGAR + ".ide_lug="
			+ DataBaseDictionary.ALIAS_PERSONALUGAR + ".ide_lug";
	
	
	public static final String GET_BASE_LUGAR_SUGERIDO = "SELECT * FROM Lugar "
			+ DataBaseDictionary.ALIAS_LUGAR + " INNER JOIN TipoLugar "
			+ DataBaseDictionary.ALIAS_TIPOLUGAR + " ON "
			+ DataBaseDictionary.ALIAS_LUGAR + ".ide_tip="
			+ DataBaseDictionary.ALIAS_TIPOLUGAR
			+ ".ide_tip INNER JOIN PersonaLugar "
			+ DataBaseDictionary.ALIAS_PERSONALUGAR + " ON "
			+ DataBaseDictionary.ALIAS_LUGAR + ".ide_lug="
			+ DataBaseDictionary.ALIAS_PERSONALUGAR + ".ide_lug WHERE "
			+ DataBaseDictionary.ALIAS_PERSONALUGAR
			+ ".ide_per IN (SELECT ide_ami FROM AmigosPorPersona "
			+ DataBaseDictionary.ALIAS_AMIGOSPORPERSONA + " WHERE ("
			+ DataBaseDictionary.ALIAS_AMIGOSPORPERSONA + ".ide_per=?) OR "
			+ DataBaseDictionary.ALIAS_PERSONALUGAR + ".ide_per = ?)";

	public static final String GET_BASE_SOLICITUD = "Select * From Solicitud "
			+ DataBaseDictionary.ALIAS_SOLICITUD + " INNER JOIN Persona "
			+ DataBaseDictionary.ALIAS_PERSONA_SOLICITANTE + " ON sol.ide_ori="
			+ DataBaseDictionary.ALIAS_PERSONA_SOLICITANTE + ".ide_per INNER JOIN Persona "
			+ DataBaseDictionary.ALIAS_PERSONA_SOLICITADO + " ON sol.ide_des="
			+ DataBaseDictionary.ALIAS_PERSONA_SOLICITADO + ".ide_per ";	
	
	//SELECT * FROM PersonaLugar pl INNER JOIN Persona p ON p.ide_per = pl.ide_per INNER JOIN Lugar l ON l.ide_lug = pl.ide_lug;
	public static final String GET_BASE_PERSONALUGAR = "SELECT * FROM PersonaLugar "+DataBaseDictionary.ALIAS_PERSONALUGAR
			+" INNER JOIN Persona "+ DataBaseDictionary.ALIAS_PERSONA+" ON "+ DataBaseDictionary.ALIAS_PERSONA+".ide_per ="+ DataBaseDictionary.ALIAS_PERSONALUGAR+".ide_per"
			+" INNER JOIN Lugar "+ DataBaseDictionary.ALIAS_LUGAR+ " ON "+ DataBaseDictionary.ALIAS_LUGAR+".ide_lug = "+ DataBaseDictionary.ALIAS_PERSONALUGAR+".ide_lug"
			+" INNER JOIN TipoLugar "+ DataBaseDictionary.ALIAS_TIPOLUGAR + " ON "+ DataBaseDictionary.ALIAS_TIPOLUGAR+".ide_tip ="+DataBaseDictionary.ALIAS_LUGAR+".ide_tip"
			+" WHERE";
	public static final String CHECK_SOLICITUD_AMISTAD_EXISTENTE = "SELECT ide_app FROM AmigosPorPersona "
			+ DataBaseDictionary.ALIAS_AMIGOSPORPERSONA
			+ " WHERE (ide_per=? AND ide_ami=?) OR (ide_ami=? AND ide_per=?) UNION SELECT ide_sol FROM Solicitud "
			+ DataBaseDictionary.ALIAS_SOLICITUD
			+ " WHERE (ide_ori=? AND ide_des=?) OR (ide_des=? AND ide_ori=?)";
	public static final String GET_LAST_LUGAR="SELECT MAX(ide_lug) as ide_lug FROM Lugar";
	public static final String GET_LAST_FOTO="SELECT MAX(ide_fot) as ide_fot FROM Foto";
	
	public static String GET_USER_BY_USERNAME = "SELECT * FROM Persona "
        + DataBaseDictionary.ALIAS_PERSONA + " WHERE usu_per = ?";

    /*public static String GET_ALL_LINEAS = "SELECT * FROM Linea_De_Autobus "
            + DataBaseDictionary.ALIAS_LINEAAUTOBUS + " WHERE act_lin=1 ORDER BY ide_lin";
    public static String GET_BASE_LINEAS = "SELECT * FROM Linea_De_Autobus "
            + DataBaseDictionary.ALIAS_LINEAAUTOBUS + " WHERE act_lin=1";
    public static String GET_LINEAS_BY_ID = "";*/

   

    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="INSERTs">
       //public static String INSERT_SOLICITUD="INSERT INTO Solicitud (fec_sol,ide_ori,ide_des,res_sol) VALUES(TO_DATE(?,'YYYY-MM-DD'),?,?,?)";
       public static String INSERT_SOLICITUD="INSERT INTO Solicitud (fec_sol,ide_ori,ide_des,res_sol) VALUES(?,?,?,?)";

       public static String INSERT_LUGAR = "INSERT INTO Lugar (nom_lug,des_lug,lat_lug,lon_lug,ide_tip) VALUES (?,?,?,?,?)";
       public static String INSERT_LUGAR_AGREGADO = "INSERT INTO PersonaLugar (fec_plu,ide_per,ide_lug) VALUES (?,?,?)";

       public static String INSERT_COMENTARIO = "INSERT INTO Comentario VALUES(?,?,?,?,?,?)";

       
       public static String INSERT_AMIGOSPORPERSONA = "INSERT INTO AmigosPorPersona VALUES(DEFAULT,?,?)";

       public static String INSERT_FOTO = "INSERT INTO Foto (url_fot,ide_per,ide_lug) VALUES (?,?,?)";

       public static String INSERT_PERSONA ="INSERT INTO Persona (usu_per,nom_per,app_per,apm_per,cor_per,sex_per,fec_per,fot_per,pas_per) VALUES (?,?,?,?,?,?,?,?,?)";
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="UPDATEs">
       
       public static String UPDATE_SOLICITUD = "UPDATE Solicitud SET res_sol = ?, fre_sol = ? WHERE ide_sol = ?";
       public static String UPDATE_LUGAR_FOTO_URL = "UPDATE Lugar SET fot_lug = ? WHERE ide_lug = ?";
    
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="DELETEs">
   
    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="Nombres y Tipos de Columnas">
    public static String COLUMN_FILTER_NAME_PERSONA_ID = "ide_per = ?";
    public static String COLUMN_TYPE_PERSONA_ID = DataBaseDictionary.INTEGER_TYPE;
    
    //Filtros para Busqueda de Personas
    public static String COLUMN_FILTER_NAME_PERSONA_NOMBREUSUARIO ="usu_per = ?";
    public static String COLUMN_TYPE_PERSONA_NOMBREUSUARIO = DataBaseDictionary.STRING_SIMPLE;
    public static String COLUMN_FILTER_NAME_PERSONA_PASSWORD ="pas_per = ?";
    public static String COLUMN_TYPE_PERSONA_PASSWORD = DataBaseDictionary.STRING_SIMPLE;
    public static String COLUMN_FILTER_NAME_PERSONA_NOMBREUSUARIO_LIKE = "usu_per LIKE ?";
    public static String COLUMN_TYPE_PERSONA_NOMBREUSUARIO_LIKE = DataBaseDictionary.STRING_DOBLE_JOKER;
    public static String COLUMN_FILTER_NAME_PERSONA_CORREO = "cor_per LIKE ?";
    public static String COLUMN_TYPE_PERSONA_CORREO = DataBaseDictionary.STRING_DOBLE_JOKER;
    public static String COLUMN_FILTER_NAME_PERSONA_NOMBREPERSONA = "nom_per LIKE ?";
    public static String COLUMN_TYPE_PERSONA_NOMBREPERSONA = DataBaseDictionary.STRING_JOKER;
    public static String COLUMN_FILTER_NAME_PERSONA_APELLIDO_PATERNO = "app_per LIKE ?";
    public static String COLUMN_TYPE_PERSONA_APELLIDO_PATERNO = DataBaseDictionary.STRING_JOKER;
    public static String COLUMN_FILTER_NAME_PERSONA_APELLIDO_MATERNO = "apm_per LIKE ?";
    public static String COLUMN_TYPE_PERSONA_APELLIDO_MATERNO = DataBaseDictionary.STRING_JOKER;
    
    //Filtros para busqueda de lugares
    public static String COLUMN_FILTER_NAME_LUGAR_NOMBRE="nom_lug LIKE ?";
    public static String COLUMN_TYPE_LUGAR_NOMBRE = DataBaseDictionary.STRING_DOBLE_JOKER;
    public static String COLUMN_FILTER_NAME_LUGAR_ID="ide_lug = ?";
    public static String COLUMN_TYPE_LUGAR_ID = DataBaseDictionary.INTEGER_TYPE;
    public static String COLUMN_FILTER_NAME_TIPO_LUGAR_ID="ide_tip = ?";
    public static String COLUMN_TYPE_TIPO_LUGAR_ID = DataBaseDictionary.INTEGER_TYPE;
    public static String COLUMN_FILTER_NAME_LUGAR_RANKING="ran_lug>=?";
    public static String COLUMN_TYPE_LUGAR_RANKING = DataBaseDictionary.INTEGER_TYPE;

    //Filtros busqueda de solicitudes
    public static String COLUMN_FILTER_NAME_SOLICITUD_ESTADO="res_sol = ?";
    public static String COLUMN_TYPE_SOLICITUD_ESTADO = DataBaseDictionary.INTEGER_TYPE;
    public static String COLUMN_FILTER_NAME_SOLICITUD_SOLICITADO="ide_des = ?";
    public static String COLUMN_TYPE_SOLICITUD_SOLICITADO = DataBaseDictionary.INTEGER_TYPE;
    
    // Filtros de getBYFilter de Comentario
    public static String COLUMN_FILTRO_NAME_COMENTARIO_ID_COMENTARIO = "ide_com = ?";
    public static String COLUMN_TYPE_COMENTARIO_ID_COMENTARIO = DataBaseDictionary.INTEGER_TYPE;
    public static String COLUMN_FILTRO_NAME_COMENTARIO_ID_PERSONA = "ide_per = ?";
    public static String COLUMN_TYPE_COMENTARIO_ID_PERSONA = DataBaseDictionary.INTEGER_TYPE;
    public static String COLUMN_FILTRO_NAME_COMENTARIO_ID_LUGAR = "ide_lug = ?";
    public static String COLUMN_TYPE_COMENTARIO_ID_LUGAR = DataBaseDictionary.INTEGER_TYPE;
           
    
    public static String COLUMN_FILTRO_NAME_PERSONALUGAR_ID_LUGAR = "ide_plu = ?";
    public static String COLUMN_TYPE_PERSONALUGAR_ID_LUGAR = DataBaseDictionary.INTEGER_TYPE;
    
    public static String COLUMN_FILTRO_NAME_PERSONALUGAR_NOMBRE_LUGAR = "nom_lug LIKE ?";
    public static String COLUMN_TYPE_PERSONALUGAR_NOMBRE_LUGAR = DataBaseDictionary.STRING_JOKER;
    
    public static String COLUMN_FILTRO_NAME_TIPOLUGAR_TIPO_LUGAR = "des_tip LIKE ?";
    public static String COLUMN_TYPE_TIPOLUGAR_TIPO_LUGAR = DataBaseDictionary.STRING_JOKER;
    
    public static String COLUMN_FILTRO_NAME_AMIGOSPORPERSONA_ID_PERSONA = "ide_per = ?";
    public static String COLUMN_TYPE_AMIGOSPORPERSONA_ID_LUGAR = DataBaseDictionary.INTEGER_TYPE;
    
    public static String COLUMN_FILTRO_NAME_AMIGOSPORPERSONA_ID_AMIGO = "ide_ami = ?";
    public static String COLUMN_TYPE_AMIGOSPORPERSONA_ID_AMIGO = DataBaseDictionary.INTEGER_TYPE;
    
    public static String COLUMN_FILTRO_NAME_SOLICITUD_ID_DESTINO = "ide_des = ?";
    public static String COLUMN_TYPE_SOLICITUD_ID_DESTINO = DataBaseDictionary.INTEGER_TYPE;
    
    public static String DELETE_AMIGOS_POR_PERSONA = "DELETE FROM AmigosPorPersona WHERE ide_per = ? AND ide_ami = ?";
    
        
    
    
    //</editor-fold>
    //</editor-fold>
}
